package com.rh.soc.zonethirdinterface.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.rh.soc.management.usermanage.usermanage.model.UserInfoVO;
import com.rh.soc.securitydomainmanage.model.StaffInfoVO;
import com.rh.soc.securitydomainmanage.model.StaffMainInfoVO;
import com.rh.soc.zonethirdinterface.dao.IThirdStaffManageDAO;
import com.rh.webserver.common.base.dao.BaseDao;
import com.rh.webserver.common.base.exception.AppException;
import com.rh.webserver.common.config.Config;
import com.rh.webserver.common.util.CrytogramUtil;
import com.rh.webserver.common.util.UuidGet;

/**
 * 
 */
@Repository("thirdstaffmanagedao")
public class ThirdStaffManageDAOImpl extends BaseDao implements IThirdStaffManageDAO {
	/**
	 * 增加人员信息
	 * 
	 * @param staffInfoVO
	 * @return int
	
	 */
	public int addStaff(StaffInfoVO staffInfoVO) {

		String id = this.nextVal("SEQ_STAFF_DOMAIN");
		StringBuilder sql = new StringBuilder(128);
		sql.append(" INSERT INTO t_staff_domain (staff_id,ss_id,name,sex,telephone,");
		sql.append("                             mobile,email,staff_post,remark,ls_status)");
		sql.append(" VALUES (?,?,?,?,?,?,?,?,?,?)");
       //staff_post是职务字段，因为数据中是必填字段，所以价格默认值
		Object[] o = new Object[] { id, staffInfoVO.getSsId(), staffInfoVO.getName(), staffInfoVO.getSex(),
				staffInfoVO.getTelphone(), staffInfoVO.getMobile(), staffInfoVO.getEmail(), "1122",
				staffInfoVO.getRemark(), staffInfoVO.getLsStatus() };
		this.update(sql.toString(), o);
		return Integer.parseInt(id);
	}

	/**
	 * 删除人员信息
	 * 
	 * @param staffId
	 * @return int

	 */
	public int delStaff(String staffId) {
		String sql = "Delete t_staff_domain WHERE staff_id = ?";
		Object[] o = new Object[] { Integer.parseInt(staffId) };
		return this.update(sql, o);
	}

	/**
	 * 查询人员列表
	 * 
	 * @param staffInfoVO
	 * @return List<StaffInfoVO>

	 * @throws AppException
	 */
	public List<StaffInfoVO> queryStaffList(StaffInfoVO staffInfoVO) {
		StringBuilder sql = new StringBuilder(128);
		String staffId = staffInfoVO.getStaffId();
		String ssId = staffInfoVO.getSsId();
		List<Object> condition = new ArrayList<Object>();
		sql.append(" SELECT  staff_id, ss_id,name,sex,telephone,mobile,email,staff_post,remark,ls_status ");
		sql.append(" FROM    t_staff_domain ");
		sql.append(" WHERE   1=1 ");
		if (StringUtils.isNotEmpty(ssId)) {
			sql.append(" AND ss_id = ?");
			condition.add(ssId);
		}
		if (StringUtils.isNotEmpty(staffId)) {
			sql.append(" AND staff_id = ?");
			condition.add(staffId);
		}
		sql.append(" order by staff_id desc");
		if (condition.size() > 0) {

			return this.query(sql.toString(), staffInfoVO, condition.toArray());
		} else {

			return query(sql.toString(), staffInfoVO);
		}
	}

	/**
	 * 修改人员列表
	 * 
	 * @param staffInfoVO
	 * @return int

	 * @throws AppException
	 */

	public int updStaff(StaffInfoVO staffInfoVO) {
		StringBuilder sql = new StringBuilder(128);
		sql.append(" UPDATE t_staff_domain ");
		sql.append(" SET    name = ?,sex = ?,");
		sql.append("        telephone = ?,mobile = ?,email = ?,staff_post = ?,");
		sql.append("        remark = ?,ls_status = ?");
		sql.append(" WHERE   staff_id = ?").toString();

		Object[] o = new Object[] { staffInfoVO.getName(), staffInfoVO.getSex(), staffInfoVO.getTelphone(),
				staffInfoVO.getMobile(), staffInfoVO.getEmail(), staffInfoVO.getStaffPost(), staffInfoVO.getRemark(),
				staffInfoVO.getLsStatus(), staffInfoVO.getStaffId() };

		return this.update(sql.toString(), o);
	}

	/**
	 * 查询组织Id
	 * 
	 * @param domaId
	 * @return String

	 * @throws AppException
	 */
	public String querySSid(String domaId) {

		String sql = "SELECT SS_ID FROM t_staff_structure WHERE DOMA_ID = ? ";
		SqlRowSet rs = this.query(sql, domaId);
		String result = "-1";
		if (rs.next()) {
			result = rs.getString(1);
		}
		return result;
	}

	/**
	 * 登录系统选择是，向t_org_USER增加记录
	 * 
	 * @param vo
	 * @return String

	 * @throws Exception 
	 */

	public String addUserInfo(StaffInfoVO vo) throws Exception {

		UserInfoVO uservo = new UserInfoVO();
		UuidGet uuidgenerator = new UuidGet();
		String pass = vo.getUserPwd();
		uservo.setUserId(uuidgenerator.getUUID());
		// 密码加密
		uservo.setUserPwd(CrytogramUtil.encryptAndBase64(pass, Config.USER_ENC_ALGORITHM));
		uservo.setUserAccountenabled("T");
		uservo.setUserAccountLocked("F");

		String mainSql = new StringBuffer("INSERT INTO t_org_user (user_id,user_account,user_fullname,user_password,")
				.append("user_account_enabled,user_account_locked,staff_id) VALUES(?,?,?,?,?,?,?)").toString();
		String subSql = new StringBuffer(
				"INSERT INTO t_org_USER_EXT(USER_ID,USER_EMAIL,USER_PHONE,USER_MOBILEPHONE,USER_ROLE ) VALUES(?,?,?,?,?)")
						.toString();

		this.update(mainSql, uservo.getUserId(), vo.getUserAccount(), vo.getName(), uservo.getUserPwd(),
				uservo.getUserAccountenabled(), uservo.getUserAccountLocked(), vo.getStaffId());
		this.update(subSql, uservo.getUserId(), uservo.getUserEmail(), uservo.getUserPhone(),
				uservo.getUserMobilePhone(), uservo.getRoles());
		return uservo.getUserId();

	}

	/**
	 * 如果某个人员登录系统为是，则查询出该人员的登录账号和密码
	 * 
	 * @param staffId
	 * @return

	 */
	public UserInfoVO queryLoginInfo(StaffInfoVO staffInfoVO) {
		String staffId = staffInfoVO.getStaffId();
		UserInfoVO vo = new UserInfoVO();
		StringBuilder sql = new StringBuilder(128);
		sql.append("SELECT user_id, user_account, user_fullname,user_password FROM t_org_user WHERE staff_id = ?");
		Object[] o = new Object[] { staffId };
		SqlRowSet rs = query(sql.toString(), o);
		if (rs.next()) {
			vo.setUserId(rs.getString("user_id"));
			vo.setUserAccount(rs.getString("user_account"));
			vo.setUserName(rs.getString("user_fullname"));
			vo.setUserPwd(rs.getString("user_password"));

		}
		return vo;

	}

	/**
	 * 如果某个人员登录系统为是，并且具有安全角色，则查询对应的安全角色Id
	 * 
	 * @param vo
	 * @return

	 */
	public List<StaffInfoVO> queryRoleId(StaffInfoVO staffInfoVO) {
		StringBuilder sql = new StringBuilder(128);
		List<Object> condition = new ArrayList<Object>();
		// 获得传进来的userId
		String userId = staffInfoVO.getUserId();
		sql.append("SELECT a.role_id,b.role_name ");
		sql.append(" FROM t_org_user_busirole a,t_org_businessrole b ");
		sql.append(" WHERE a.role_id=b.role_id  ");
		sql.append(" AND user_id = ? ");
		condition.add(userId);
		SqlRowSet rs = this.query(sql.toString(), condition.toArray());
		List<StaffInfoVO> voList = new ArrayList<StaffInfoVO>();
		while (rs.next()) {
			StaffInfoVO vo = new StaffInfoVO();
			vo.setSecureRole(rs.getString("role_id"));
			vo.setSecureRoleName(rs.getString("role_name"));
			voList.add(vo);
		}
		return voList;

	}

	/**
	 * 如果某个人员登录系统由是改为否，则删除当前该人员在t_org_USER的记录
	 * 
	 * @param staffId
	 * @return int

	 */
	public int delUserLoginInfo(String staffId) {

		StringBuilder sql = new StringBuilder(128);
		sql.append("Delete t_org_user WHERE staff_id = ?");
		Object[] o = new Object[] { staffId };
		return this.update(sql.toString(), o);
	}

	/**
	 * 查询登录用户详细信息
	 * 
	 * @param userInfoVO
	 * @return

	 */
	public UserInfoVO queryUserDetail(UserInfoVO userInfoVO) {
		String sql = "SELECT user_id,user_account  FROM t_org_user WHERE 1=1 and user_account = ? ";
		List<UserInfoVO> list = null;
		userInfoVO.setPageSize(10);
		userInfoVO.setCurrentPageNum(1);
		list = query(sql, userInfoVO, new Object[] { userInfoVO.getUserAccount() });
		if (list.size() > 0) {
			return list.get(0);
		} else {
			return new UserInfoVO();
		}
	}

	/**
	 * 插入角色id和userId详细信息
	 * 
	 * @param userInfoVO
	 * @return

	 */
	public void addRoleIdAndUserId(StaffInfoVO vo) {

		String sql = ("INSERT INTO t_org_user_busirole (role_id,user_id) VALUES(?,?)");
		Object[] o = new Object[] { vo.getSecureRole(), vo.getUserId() };
		this.update(sql, o);

	}

	/**
	 * 根据用户id删除roleId和userId关联的表
	 * 
	 * @param staffId
	 * @return

	 */

	public void delUserInfo(String userId) {

		StringBuilder mainSql = new StringBuilder(128);
		mainSql.append("Delete   t_org_user ");
		mainSql.append("WHERE user_id = ?").toString();
		StringBuilder subSql = new StringBuilder(128);
		subSql.append("Delete   t_org_USER_EXT ");
		subSql.append("WHERE user_id = ?").toString();
		this.update(mainSql.toString(), userId);
		this.update(subSql.toString(), userId);
	}

	/**
	 * 根据人员信息VO 查询出所有的表关联Id
	 * 
	 * @param staffInfoVO
	 * @return

	 */
	public List<StaffMainInfoVO> queryStaffMainInfoList(StaffMainInfoVO smvo) {
		StringBuilder sql = new StringBuilder(128);
		// 获得传进来的staffId
		String staffId = smvo.getStaffId();
		sql.append(" SELECT a.user_id, c.role_id, b.staff_id ");
		sql.append(" FROM   t_staff_domain b left join t_org_user a on a.staff_id = b.staff_id  ");
		sql.append("        left join t_org_user_busirole c ");
		sql.append("        on a.user_id = c.user_id ");
		sql.append(" WHERE  b.staff_id = ? ");
		List<StaffMainInfoVO> list = new ArrayList<StaffMainInfoVO>();
		smvo.setPageSize(10);
		smvo.setCurrentPageNum(1);
		list = query(sql.toString(), smvo, staffId);

		return list;
	}

	/**
	 * 根据userId删除角色Id和userId关联表的记录
	 * 
	 * @param staffInfoVO
	 * @return

	 */
	public int delRoleId(String userId) {
		int rtn = 0;
		StringBuilder sql = new StringBuilder(128);
		sql.append(" Delete FROM t_org_user_busirole ");
		sql.append(" WHERE user_id = ? ");
		this.update(sql.toString(), userId);
		return rtn;

	}

	/**
	 * 修改用户表信息
	 * 
	 * @param staffInfoVO
	 * @return

	 * @throws Exception 
	 */
	public int updUser(StaffInfoVO staffInfoVO) throws Exception {
		List<Object> con = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder(128);
		sql.append(" UPDATE  t_org_user ");
		sql.append(" SET user_account = ?, ");
		con.add(staffInfoVO.getUserAccount());
		sql.append(" user_fullname = ? ");
		con.add(staffInfoVO.getName());
		if (StringUtils.isNotEmpty(staffInfoVO.getUserPwd())) {
			sql.append(",user_password = ? ");
			con.add(CrytogramUtil.encryptAndBase64(staffInfoVO.getUserPwd(), Config.USER_ENC_ALGORITHM));
		}
		sql.append(" WHERE user_id = ? ").toString();
		con.add(staffInfoVO.getUserId());
		int rs = update(sql.toString(), con.toArray());
		return rs;
	}

	/**
	 * 
	 * 判断改人员是否存在
	 * 
	 * @param staffId
	 * @return

	 */
	public int checkStaffExists(String staffId) {
		String sql = "SELECT staff_id FROM t_staff_domain WHERE staff_id = ? ";
		SqlRowSet rs = this.query(sql, staffId);
		int result = 0;
		if (rs.next()) {
			rs.getString(1);
			result = 1;

		}
		return result;

	}
}
